﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Security.Cryptography;

namespace ProcessDB
{
    public class GetDB
    {
        string connectionString = "Server=localhost;Port=3306;Database=test;Uid=root;Pwd=\"\";";
        //string connectionString = "Server=yugioh.vn;Port=3306;Database=test;Uid=adminyugi;Pwd=admin123;";
        //string connectionString = "Server=localhost;Port=3306;Database=yugioh;Uid=adminyugi;Pwd=admin123;";
        MySqlConnection connection;
        Dictionary<int, string> TempNickStore = new Dictionary<int, string>();
        Dictionary<int, string> TempCardNameStore = new Dictionary<int, string>();
        public GetDB()
        {
            connection = new MySqlConnection(connectionString);
        }
        void Connect()
        {
            connection.Open();
        }
        void Disconnect()
        {
            connection.Close();
        }
        bool Ping()
        {
            return connection.Ping();
        }
        #region Method
        public string GetNickName(int ID)
        {
            if (TempNickStore.ContainsKey(ID))
                return TempNickStore[ID];
            string commandString = "select NickName from user where ID=" + ID.ToString();
            MySqlCommand command = new MySqlCommand(commandString, connection);
            MySqlDataReader reader = command.ExecuteReader();            
            if (reader.FieldCount > 1)
                return "error";
            while (reader.Read())
            {
                string nick=reader.GetString(0);
                TempNickStore[ID] = nick;
                reader.Close();
                return nick;
            }
            reader.Close();
            return "";
        }

        public string GetCardName(int CardID)
        {
            if (TempCardNameStore.ContainsKey(CardID))
                return TempCardNameStore[CardID];
            string commandString = "select Name from `card` where ID=" + CardID.ToString();
            MySqlCommand command = new MySqlCommand(commandString, connection);
            MySqlDataReader reader = command.ExecuteReader();
            if (reader.FieldCount > 1)
                return "error";
            while (reader.Read())
            {
                string name = reader.GetString(0);
                TempCardNameStore[CardID] = name;
                reader.Close();
                return name;
            }
            reader.Close();
            return "";
        }
        public string GetCardsOfPlayer(int IDPlayer)
        {
            PlayerInfo info = GetDeckStore(IDPlayer);
            string result = "";
            foreach (var item in info.Storage)
            {
                result += item + ",";
            }
            result += "\n";
            foreach (var item in info.Deck)
            {
                result += item + ",";
            }
            return result;
        }

        #region Login-Logout
        /// <summary>
        /// Login User
        /// </summary>
        /// <param name="Email">Email to Auth</param>
        /// <param name="password">Password to Auth</param>
        /// <param name="auto_update">Auto updateTable When Login Success</param>
        /// <returns></returns>
        public PlayerInfo Login(string Email, string password, bool auto_update)
        {
           
            string commandString = "select * from user where Email='" + Email.ToString()+"'";
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
                MySqlDataReader reader = command.ExecuteReader();
                PlayerInfo info= null;
                if (reader.HasRows)
                {
                    reader.Read();
                    info = new PlayerInfo();
                    info.ID = reader.GetInt32("ID");
                    info.Email = reader.GetString("Email");
                    info.Password = reader.GetString("Password");
                    info.NickName = reader.GetString("NickName");
                    reader.Close();
                }
                else
                {
                    return null;
                }
            Disconnect();

            SHA1 hasher= SHA1.Create();
            byte[] res= hasher.ComputeHash(GetByte(password));
            if (info.Password == GetString(res))
            {
                if(auto_update)
                    UpdateOnline(info.ID, true);
               
            }

            return info;
        }
        public void Logout(int ID)
        {
            UpdateOnline(ID, false);
        }
        private void UpdateOnline(int ID,bool value)
        {
            string commandString = String.Format("UPDATE user SET IsOnline='{0}' WHERE (ID={1})",value?1:0,ID);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            int res= command.ExecuteNonQuery();
            if (res == 0)
                Console.WriteLine("Error When Excute : {0}", commandString);
            Disconnect();
        }
        #endregion
        #region Table
        public List<TableInfo> GetAllTable(int RoomID)
        {
            string commandString =string.Format("SELECT * FROM `table` where PlayerID1 in (SELECT ID FROM `user` where RoomID ='{0}') AND PlayerID2 = '-1'", RoomID);
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            TableInfo info = null;
            List<TableInfo> res = new List<TableInfo>();
            if (reader.HasRows)
            {
                while(reader.Read())
                {
                    info = new TableInfo();
                    info.TableID = reader.GetInt32("TableID");
                    info.PlayerID1 = reader.GetInt32("PlayerID1");
                    info.PlayerID2 = reader.GetInt32("PlayerID2");
                    try {info.Payment = reader.GetInt32("Payment");}
                    catch{ info.Payment = 0;}
                    try { info.CardID = reader.GetInt32("CardID"); }
                    catch { info.CardID = -1; }
                    try { info.Comment = reader.GetString("Comment"); }
                    catch { info.Comment = ""; }
                    res.Add(info);
                }
                reader.Close();
                foreach (var tmp in res)
                {
                    tmp.NickName1 = GetNickName(tmp.PlayerID1);
                    tmp.NickName2 = GetNickName(tmp.PlayerID2);
                    if (tmp.CardID != -1)
                        tmp.CardName = GetCardName(tmp.CardID);
                    else
                        tmp.CardName = "";
                }
            }
            else
            {
                Disconnect();
                return null;
            }
            Disconnect();
            return res;
        }
        #endregion
        #region Deck & Storage
        public PlayerInfo GetDeckStore(int id_player)
        {

            string commandString = "select * from cardowner where IDPlayer='" + id_player.ToString() + "'";
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            MySqlDataReader reader = command.ExecuteReader();
            PlayerInfo info = new PlayerInfo();
            info.ID = id_player;
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int soluongsohuu = (int)reader["SoLuongSoHuu"];
                    int soluongtrongbobai = (int)reader["SoLuongTrongBoBai"];
                    int type_id = (int)reader["IDCard"];
                    for (int i = 0; i < soluongsohuu; i++)
                    {
                        info.Storage.Add(type_id);
                    }
                    for (int i = 0; i < soluongtrongbobai; i++)
                    {
                        info.Deck.Add(type_id);
                    }
                }
                reader.Close();
            }
            else
            {
                Disconnect();
                return null;
            }
            Disconnect();
            return info;
        }
        #endregion

        #region Forbidden
        public List<ForbiddenInfo> GetAllForbidden()
        {
            string commandString = "SELECT * FROM `forbidden_card`";
            MySqlCommand command = new MySqlCommand(commandString, connection);
            Connect();
            List<ForbiddenInfo> result = new List<ForbiddenInfo>();
            MySqlDataReader reader = command.ExecuteReader();
            ForbiddenInfo info = null;
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    info = new ForbiddenInfo();
                    info.ID = reader.GetInt32("ID");
                    info.MaxNumber = reader.GetInt32("Max_Number_InDeck");
                    result.Add(info);
                }
                reader.Close();
            }
            else
            {
                Disconnect();
                return null;
            }
            Disconnect();
            return result;
        }
        #endregion
        #endregion
        //------------------------------Util---------------------------
        #region Util
            private byte[] GetByte(String value)
            {
                return UTF8Encoding.UTF8.GetBytes(value);
            }
            private string GetString(byte[] value)
            {
                StringBuilder res = new StringBuilder();
                foreach (byte item in value)
                {
                    res.Append(item.ToString("X2"));
                }
                return res.ToString();

            }
        #endregion
        #region Room
            public List<RoomInfo> GetAllRoom()
            {
                string commandString = "SELECT * FROM `room`";
                MySqlCommand command = new MySqlCommand(commandString, connection);
                Connect();
                MySqlDataReader reader = command.ExecuteReader();
                RoomInfo info = null;
                List<RoomInfo> res = new List<RoomInfo>();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        info = new RoomInfo();
                        info.RoomID = reader.GetInt32("RoomID");
                        info.Name = reader.GetString("Name");
                        res.Add(info);
                    }
                    reader.Close();
                    UpdateCount(res);
                }
                else
                {
                    Disconnect();
                    return null;
                }
                Disconnect();
                return res;
            }
            public void UpdateCount(List<RoomInfo> list)
            {
                foreach (RoomInfo roominfo in list)
                {
                    roominfo.CountUser = CountUser(roominfo.RoomID);
                    roominfo.CountCreater = CountCreater(roominfo.RoomID);
                }
            }
            public int CountUser(int RoomID)
            {
                String CommandString = string.Format("select count(*) as total from `user` where RoomID = '{0}'", RoomID);
                MySqlCommand Command = new MySqlCommand(CommandString, connection);
                int count = 0;
                MySqlDataReader reader = Command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        count = reader.GetInt32("total");
                    }
                    reader.Close();
                }
                else return -1;
                return count;
            }
            public int CountCreater(int RoomID)
            {
                String CommandString = string.Format("select count(*) as total from `user` inner join `table` on ID = PlayerID1 where RoomID = '{0}' AND PlayerID2 = '-1'", RoomID);
                MySqlCommand Command = new MySqlCommand(CommandString, connection);
                int count = 0;
                MySqlDataReader reader = Command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        count = reader.GetInt32("total");
                    }
                    reader.Close();
                }
                else return -1;
                return count;
            }
        #endregion
        #region User
            public List<PlayerInfo> GetAllUser(int RoomID)
            {
                string commandString = string.Format("SELECT * FROM `user` where RoomID = '{0}'", RoomID);
                MySqlCommand command = new MySqlCommand(commandString, connection);
                Connect();
                MySqlDataReader reader = command.ExecuteReader();
                PlayerInfo info = null;
                List<PlayerInfo> res = new List<PlayerInfo>();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        info = new PlayerInfo();
                        info.ID = reader.GetInt32("ID");
                        info.Level = reader.GetInt32("Level");
                        info.NickName = reader.GetString("NickName");
                        info.IsOnline = reader.GetInt16("IsOnline");
                        res.Add(info);
                    }
                    reader.Close();

                }
                else
                {
                    Disconnect();
                    return null;
                }
                Disconnect();
                return res;
            }
            public List<PlayerInfo> GetAllFriend(int UserID)
            {
                string commandString = string.Format("SELECT * from `user` where ID in (SELECT UserID2 FROM friends where UserID1 = '{0}') UNION SELECT * from `user` where ID in (SELECT UserID1 FROM friends where UserID2 = '{1}')", UserID, UserID);
                MySqlCommand command = new MySqlCommand(commandString, connection);
                Connect();
                MySqlDataReader reader = command.ExecuteReader();
                PlayerInfo info = null;
                List<PlayerInfo> res = new List<PlayerInfo>();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        info = new PlayerInfo();
                        info.ID = reader.GetInt32("ID");
                        info.Level = reader.GetInt32("Level");
                        info.NickName = reader.GetString("NickName");
                        info.IsOnline = reader.GetInt16("IsOnline");
                        res.Add(info);
                    }
                    reader.Close();

                }
                else
                {
                    Disconnect();
                    return null;
                }
                Disconnect();
                return res;
            }
        #endregion

    }
}
